package com.shzu.jwc.ty.common;  
  
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;  
  
public class ExportExcelUtils {  
      
    private String title; // 导出表格的表名  
      
    private String[] rowName;// 导出表格的列名  
      
    private List<Object[]>  dataList = new ArrayList<Object[]>(); // 对象数组的List集合  
      
    private HttpServletResponse  response;  
      
  
    // 传入要导入的数据  
    public ExportExcelUtils(String title,String[] rowName,List<Object[]>  dataList,HttpServletResponse  response){  
        this.title=title;  
        this.rowName=rowName;  
        this.dataList=dataList;  
        this.response = response;  
    }  
      
    // 导出数据  
    public void exportData(){  
        try {  
            HSSFWorkbook workbook =new HSSFWorkbook(); // 创建一个excel对象  
            HSSFSheet sheet =workbook.createSheet(title); // 创建表格  
            // 产生表格标题行  
              
            // sheet样式定义  
            /**  
             * 参数说明  
             * 从0开始   第一行 第一列 都是从角标0开始  
             * 行 列 行列    (0,0,0,5)  合并第一行 第一列  到第一行 第六列  
             * 起始行，起始列，结束行，结束列  
             *   
             * new Region()  这个方法使过时的  
             */  
              
            int columnNum = rowName.length;  // 表格列的长度  
            HSSFRow rowRowName = sheet.createRow(0);  // 在第二行创建行  
            HSSFCellStyle cells =workbook.createCellStyle();  
            cells.setBottomBorderColor(HSSFColor.BLACK.index);    
            rowRowName.setRowStyle(cells);  
              
            // 循环 将列名放进去  
            for (int i = 0; i < columnNum; i++) {  
                HSSFCell  cellRowName = rowRowName.createCell((int)i);  
                cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); // 单元格类型  
                  
                HSSFRichTextString text = new HSSFRichTextString(rowName[i]);  // 得到列的值  
                cellRowName.setCellValue(text); // 设置列的值  
            }  
              
            // 将查询到的数据设置到对应的单元格中  
            for (int i = 0; i < dataList.size(); i++) {  
                Object[] obj = dataList.get(i);//遍历每个对象  
                HSSFRow row = sheet.createRow(i+1);//创建所需的行数  
                for (int j = 0; j < obj.length; j++) {  
                     HSSFCell  cell = null;   //设置单元格的数据类型   
                     if(j==0){    
                         cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);  
                         cell.setCellValue(obj[0]==null?"":obj[0].toString());
                     }else if(j==1) {
                    	 cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
                    	 cell.setCellValue(obj[1]==null?"":obj[1].toString());
                     }else if(j==2) {
                    	 cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
                    	 cell.setCellValue(obj[2]==null?"":obj[2].toString());
                     }else if(j==3) {
                    	 cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
                    	 cell.setCellValue(obj[3]==null?"":obj[3].toString());
                     }else if(j==4) {
                    	 cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
                    	 cell.setCellValue(obj[4]==null?"":obj[4].toString());
                     }else if(j==5) {
                         cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
                         cell.setCellValue(obj[5]==null?"":obj[5].toString());
                     }else if(j==6) {
                         cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
                         cell.setCellValue(obj[6]==null?"":obj[6].toString());
                     }
                     else if(j==7) {
                         cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
                         cell.setCellValue(obj[7]==null?"":obj[7].toString());
                     }
                     else if(j==8) {
                    	 cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
                    	 cell.setCellValue(obj[8]==null?"":obj[8].toString());
                     }else if(j==9) {
                    	 cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
                    	 cell.setCellValue(obj[9]==null?"":obj[9].toString());
                     } else if(j==10) {
                         cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
                         cell.setCellValue(obj[10]==null?"":obj[10].toString());
                     }else if(j==11) {
                         cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
                         cell.setCellValue(obj[11]==null?"":obj[11].toString());
                     }else if(j==12) {
                         cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
                         if(obj[12]!=null)
                             cell.setCellValue(obj[12]==null?"":obj[12].toString());
                         else
                             cell.setCellValue("");
                     }else if(j==14) {
                    	 cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);
                    	 if(obj[14]!=null)
                    	    cell.setCellValue((double) obj[14]);
                    	 else
                             cell.setCellValue("");
                     }else if(j==17) {
                    	 cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);
                    	 if(obj[17]!=null)
                    		 cell.setCellValue((double) obj[17]);
                    	 else
                    		 cell.setCellValue("");
                     }else if(j==19) {
                         cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
                         if(obj[19]!=null)
                             cell.setCellValue(obj[19]==null?"":obj[19].toString());
                         else
                             cell.setCellValue("");
                     }else if(j==20) {
                         cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);
                         if(obj[20]!=null)
                             cell.setCellValue((double) obj[20]);
                         else
                             cell.setCellValue("");
                     }else if(j==22) {
                         cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
                         if(obj[22]!=null)
                             cell.setCellValue(obj[22]==null?"":obj[22].toString());
                         else
                             cell.setCellValue("");
                     }else if(j==23){
                    	 cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);
                    	 if(obj[23]!=null)
                    		 cell.setCellValue((double) obj[23]);
                    	 else
                    		 cell.setCellValue("");
                     }else if(j==25) {
                         cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
                         if(obj[25]!=null)
                             cell.setCellValue(obj[25]==null?"":obj[25].toString());
                         else
                             cell.setCellValue("");
                     } else {
                    	 cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
                    	 cell.setCellValue("");
                     }

                }  
            }  
            //  让列宽随着导出的列长自动适应  
             sheet.autoSizeColumn((short)0); //调整第一列宽度  
             sheet.autoSizeColumn((short)1); //调整第二列宽度  
             sheet.autoSizeColumn((short)2); //调整第三列宽度  
             sheet.autoSizeColumn((short)3); //调整第四列宽度  
             sheet.autoSizeColumn((short)4); //调整第五列宽度  
             sheet.autoSizeColumn((short)5); //调整第六列宽度  
               
             if(workbook !=null){    
                    try    
                    {    
                        // excel 表文件名  
                        String fileName = title + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls";    
                        String fileName11 = URLEncoder.encode(fileName,"UTF-8");  
                        String headStr = "attachment; filename=\"" + fileName11 + "\"";    
                        response.setContentType("APPLICATION/OCTET-STREAM");    
                        response.setHeader("Content-Disposition", headStr);    
                        OutputStream out = response.getOutputStream();    
                        workbook.write(out);  
                        out.flush();  
                        out.close();  
                    }    
                    catch (IOException e)    
                    {    
                        e.printStackTrace();    
                    }   
                      
                }    
        
            }catch(Exception e){    
                e.printStackTrace();    
            }    
                
        }  
}
              
      
